﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using 文件内容队列读写.DataQueue;

namespace 文件内容队列读写
{
    /// <summary>
    /// 终端数据库
    /// </summary>
    public class TerminalDbData
    {
        /// <summary>
        /// 终端标识
        /// </summary>
        public string TerminalFlag { get; set; }

        private SQLiteDBHelper dbHelper = null;

        /// <summary>
        /// 数据库操作相关
        /// </summary>
        public SQLiteDBHelper DbHelper
        {
            get { return dbHelper; }
        }

        private string _dbPath = string.Empty;

        /// <summary>
        /// 创建一个新的终端数据库列表
        /// </summary>
        /// <param name="dbPath"></param>
        public TerminalDbData(string dbPath)
        {
            _dbPath = dbPath;
            dbHelper = new SQLiteDBHelper(dbPath);
            InitTerminalDB();
        }

        object lockObject = new object();
        /// <summary>
        /// 当前数据库是否可操作
        /// </summary>
        public bool CanOperation { get; set; }

        /// <summary>
        /// 开始DB
        /// </summary>
        public void BeginDB()
        {
            lock (lockObject)
            {
                if (!System.IO.File.Exists(_dbPath)) { return; }

                if (connection == null)
                {
                    connection = new SQLiteConnection("Data Source=" + _dbPath);
                }
                if (connection.State != System.Data.ConnectionState.Open)
                {
                    connection.Open();
                }
                if (transaction == null)
                {
                    transaction = connection.BeginTransaction();
                }
                if (command == null)
                {
                    command = new SQLiteCommand(connection);
                }
                CanOperation = true;
            }
        }

        /// <summary>
        /// 批量写入到库中
        /// </summary>
        /// <param name="sqls">需要批处理的SQL</param>
        /// <returns></returns>
        public void BatchExecuteInsert(List<string> sqls)
        {
            lock (lockObject)
            {
                foreach (var sql in sqls)
                {
                    command.CommandText = sql;
                    affectedRows += command.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// 返回一个DataReader,读取数据
        /// </summary>
        /// <param name="sql">查询用的SQL</param>
        /// <returns>返回的数据Reader对像</returns>
        public SQLiteDataReader GetReader(string sql)
        {
            if (connection == null) { return null; }
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(connection) { CommandText = sql };
                return cmd.ExecuteReader();
            }
            catch {
                return null;
            }

        }

        #region 参数定义
        int affectedRows = 0;
        SQLiteConnection connection;
        System.Data.Common.DbTransaction transaction;
        SQLiteCommand command;
        #endregion
        /// <summary>
        /// 消耗时间
        /// </summary>
        System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
        /// <summary>
        /// 提交数据,返回受影响的行数
        /// </summary>
        /// <returns>返回受影响的行数</returns>
        public int Commit(bool canBeginTransaction=true)
        {
            sw.Restart();
            int resutl = affectedRows;
            affectedRows = 0;
            lock (lockObject)
            {
                //不开始线程
                if (!canBeginTransaction)
                {
                    if (connection != null)
                    {
                        transaction.Commit();
                        transaction.Dispose();
                        transaction = null;
                        command.Dispose();
                        command = null;

                        connection.Close();
                        connection.Dispose();
                        connection = null;

                        CanOperation = true;
                    }
                }
                else
                {
                    if (command != null)
                    {
                        //
                        //command.Dispose();
                        //command = null;
                        //当有数据执行时,提交事务,
                        if (resutl > 0)
                        {
                            transaction.Commit();
                            transaction.Dispose();
                            transaction = null;
                            transaction = connection.BeginTransaction();
                        }
                        //connection.Close();
                        //connection.Dispose();
                        //connection = null;
                    }
                }
                CanOperation = false;
            }
            sw.Stop();
            if (resutl > 0)
            {
                Console.WriteLine("提交事务共:{0}条,用时{1}毫秒", resutl, sw.ElapsedMilliseconds);
            }
            return resutl;
        }

        #region 处理逻辑相关
        private System.Collections.Concurrent.ConcurrentQueue<string> writeQueue = new System.Collections.Concurrent.ConcurrentQueue<string>();
        /// <summary>
        /// 步骤1,接收数据
        /// 写队列 
        /// </summary>
        public System.Collections.Concurrent.ConcurrentQueue<string> WriteQueue
        {
            get { return writeQueue; }
        }
        private System.Collections.Concurrent.ConcurrentQueue<TerminalData> readerQueue = new System.Collections.Concurrent.ConcurrentQueue<TerminalData>();
        /// <summary>
        ///步骤2,读取解析数据
        /// 已经读取到用于发送的数据
        /// 读取字典用于去重处理,下次读取ID需要大于此id
        /// </summary>
        public long MaxReaderID { get; set; }

        /// <summary>
        /// 最大读取队列长度限制
        /// </summary>
        private long readLimitNumber = 200;
        /// <summary>
        /// 最大读取队列长度限制
        /// </summary>
        public long ReadLimitNumber
        {
            get { return readLimitNumber; }
            set { readLimitNumber = value; }
        }
        /// <summary>
        ///步骤2,读取解析数据
        /// 已经读取到用于发送的数据
        /// </summary>
        public System.Collections.Concurrent.ConcurrentQueue<TerminalData> ReaderQueue
        {
            get { return readerQueue; }
        }


        private System.Collections.Concurrent.ConcurrentQueue<string> delQueue = new System.Collections.Concurrent.ConcurrentQueue<string>();
        /// <summary>
        /// 步骤3,当发送数据收到应答后,需要删除数据
        /// 删除队列,key 用于删除的SQL
        /// </summary>
        public System.Collections.Concurrent.ConcurrentQueue<string> DelQueue
        {
            get { return delQueue; }
        }
        #endregion

        /// <summary>
        /// 数据库路径
        /// </summary>
        public string DbPath { get; set; }

        #region 数据库相关
        string createTable = @"CREATE TABLE TerminalData(
   ID INTEGER  PRIMARY KEY AUTOINCREMENT      NOT NULL,
   数据           TEXT    NOT NULL,
   终端号         NVARCHAR(100)     NOT NULL,
   写入时间        CHAR(15)
);";
        #endregion
        /// <summary>
        /// 初始化终端数据-数据库
        /// </summary>
        protected void InitTerminalDB()
        {
            try
            {            //1.创建数据库
                int n = dbHelper.ExecuteNonQuery(createTable, null);
            }
            catch { }
            try
            {            //1.设置数据库自动收缩
                int n = dbHelper.ExecuteNonQuery("PRAGMA auto_vacuum = 1", null);
            }
            catch { }
        }
    }
}
